''' 学生相关的处理程序 '''
from http.client import responses

from jinja2 import TemplateNotFound
import tornado
from tornado import web, gen
from tornado.httputil import url_concat
from tornado.options import options

import os
import shutil
import json
import logging
import pymysql

from .base import BaseHandler
from utils.dbutil import MySQLConnection
from utils.pathutil import PathBuilder

class StudentsHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self):
        user = self.get_current_user()
        self.render('students.html', user=user)

class StudentHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self):
        search_name = self.get_argument('name', "")
        search_status = self.get_argument('status', "1")
        search_pageindex = int(self.get_argument("pageindex", 1))
        pagesize = int(self.get_argument("pagesize", 5))
        startindex = (search_pageindex - 1) * pagesize
        endindex = startindex + pagesize

        sql = "select SQL_CALC_FOUND_ROWS accountid, accountname, truename, status \
            from account where role=1"
        if search_name:
            sql = sql + " and (accountname like '%" + search_name + "%' or truename like '%" + search_name + "%')"
        if search_status:
            sql = sql + " and status=" + search_status
        
        sql = (sql + ' order by accountid desc limit {}, {};').format(startindex, endindex)

        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute(sql)
                search_students = cursor.fetchall()
                sql = 'SELECT FOUND_ROWS() as total'
                cursor.execute(sql)
                search_total = cursor.fetchone()
        finally:
            conn.close()

        self.write(json.dumps({'search_students': search_students, 'total':search_total['total']}, ensure_ascii=True))
        self.finish()
    
    @web.authenticated
    @gen.coroutine
    def post(self):
        result = {'code':'success', 'message':''}
        student = json.loads(self.request.body.decode())
        accountname = student.get("accountname")
        truename = student.get('truename')
        status = int(student.get('status'))
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                cursor.callproc('student_new', args=(accountname, truename, status, 0))
                cursor.execute('select @_student_new_3')
                out_params = cursor.fetchone()
                conn.commit()
                accountid = out_params['@_student_new_3']
                result['message'] = {'accountid':accountid}

                # 创建学生工作目录
                student_dir = PathBuilder.get_work_dir_for_student(accountname)
                if not os.path.exists(student_dir):                    
                    template_dir = PathBuilder.get_template_dir()
                    shutil.copytree(template_dir, student_dir)
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'新增学生失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()

    @web.authenticated
    @gen.coroutine
    def put(self):
        result = {'code':'success', 'message':''}
        student = json.loads(self.request.body.decode())
        accountid = int(student.get('accountid'))
        truename = student.get('truename')
        status = int(student.get('status'))
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = "update account set truename='{}', status={} where accountid={}"
                sql_formatted = sql.format(truename, status, accountid)
                cursor.execute(sql_formatted)
                conn.commit()
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'修改学生信息失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()

    @web.authenticated
    @gen.coroutine
    def delete(self):
        result = {'code':'success', 'message':''}
        accountid = self.get_argument("id")
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = "select accountname, status from account where accountid={}".format(accountid)
                cursor.execute(sql)
                account = cursor.fetchone()
                accountname = account['accountname']
                if account['status'] == 1:                   
                    result['code'] = 'error'
                    result['message'] = '无法删除有效状态的学生信息'
                else:                                       
                    sql = "delete from classstudent where accountid=" + accountid
                    cursor.execute(sql)
                    sql = "delete from account where accountid=" + accountid
                    cursor.execute(sql)
                    conn.commit()

                    # 从磁盘上删除
                    student_dir = PathBuilder.get_work_dir_for_student(accountname)
                    if os.path.exists(student_dir):
                        shutil.rmtree(student_dir)
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'删除学生数据失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()

class StudentClassHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self):
        accountid = self.get_argument('id')

        sql = "select a.classid, a.classname, b.courseid, b.coursename \
            from class as a inner join course as b on a.courseid=b.courseid \
            inner join classstudent as c on a.classid = c.classid \
            where c.accountid=" + accountid

        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute(sql)
                results = cursor.fetchall()
        finally:
            conn.close()

        self.write(json.dumps(results, ensure_ascii=True))
        self.finish()


default_handlers = [
    (r'/students', StudentsHandler),
    (r'/student_studentsearch', StudentHandler),
    (r'/student_newstudent', StudentHandler),
    (r'/student_editstudent', StudentHandler),
    (r'/student_deletestudent', StudentHandler),
    (r'/student_getstudentclasses', StudentClassHandler),
]